﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows;

namespace SqlSugerBasic
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        SqlSugarClient db;
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            db = new SqlSugarClient(
            new ConnectionConfig()
            {
                ConnectionString = "server=127.0.0.1;database=mini_standard_pc;uid=root;pwd=root;port = 3306;Persist Security Info=True;Charset=utf8;Allow User Variables=True;",
                DbType = DbType.MySql,//设置数据库类型mysq
                IsAutoCloseConnection = true,//自动释放数据务，如果存在事务，在事务结束后释放
                InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
            });
            //DirectoryInfo exePath = new DirectoryInfo(Environment.CurrentDirectory);
            //db.DbFirst.IsCreateAttribute().CreateClassFile(exePath.FullName, "PTF.Models.DBEntity");//文件地址，指定实体命名空间
            //db.CodeFirst.SetStringDefaultLength(200/*设置varchar默认长度为200*/).InitTables(typeof(Person));//建立表
        }

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            /*插入*/
            var insertObj = new Person() { Name = "csdn" };
            db.Insertable(insertObj).ExecuteCommand();

            //Insert reutrn Insert Count
            var t2 = db.Insertable(insertObj).ExecuteCommand();

            //Insert reutrn Identity Value
            var t3 = db.Insertable(insertObj).ExecuteReturnIdentity();

            //Only  insert  Name 
            var t4 = db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Id }).ExecuteReturnIdentity();

            //Ignore TestId
            var t5 = db.Insertable(insertObj).IgnoreColumns(it => new { it.Name, it.Id }).ExecuteReturnIdentity();

            //Insert List<T>
            var s9 = db.Insertable(new List<Person>() { insertObj }).InsertColumns(it => new { it.Name }).ExecuteCommand();
        }

        private void btnDel_Click(object sender, RoutedEventArgs e)
        {
            /*删除*/
            db.Deleteable<Person>(1).ExecuteCommand();


            //by entity
            db.Deleteable<Person>().Where(new Person() { Id = 1 }).ExecuteCommand();

            //by primary key
            db.Deleteable<Person>().In(1).ExecuteCommand();

            //by primary key array
            db.Deleteable<Person>().In(new int[] { 1, 2 }).ExecuteCommand();

            //by expression
            db.Deleteable<Person>().Where(it => it.Id == 1).ExecuteCommand();
        }

        private void btnUpd_Click(object sender, RoutedEventArgs e)
        {
            /*更新*/
            var updateObj = new Person() { Id = 1, Name = "csdn2" };
            db.Updateable(updateObj).ExecuteCommand();


            //update reutrn Update Count
            var t1 = db.Updateable(updateObj).ExecuteCommand();

            //Only  update  Name 
            var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

            //Ignore  Name and TestId
            var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.Name, it.Id }).ExecuteCommand();

            //update List<T>
            var t7 = db.Updateable(new List<Person>() { updateObj }).ExecuteCommand();
        }

        private void btnSec_Click(object sender, RoutedEventArgs e)
        {
            /*查询*/
            var list = db.Queryable<Person>().ToList();//查询所有
            var getById = db.Queryable<Person>().InSingle(1);//根据主键查询
            var total = 0;
            var getPage = db.Queryable<Person>().Where(it => it.Id == 1).ToPageList(1, 2, ref total);//根据分页查询
                                                                                                     //多表查询用法 http://www.codeisbug.com/Doc/8/1124

            //easy
            var getAll = db.Queryable<Person>().ToList();
            var getAllNoLock = db.Queryable<Person>().With(SqlWith.NoLock).ToList();
            var getByPrimaryKey = db.Queryable<Person>().InSingle(2);
            var sum = db.Queryable<Person>().Sum(it => it.Id);
            var isAny = db.Queryable<Person>().Where(it => it.Id == -1).Any();
            var isAny2 = db.Queryable<Person>().Any(it => it.Id == -1);
            //var getListByRename = db.Queryable<School>().AS("Person").ToList();
            var getByWhere = db.Queryable<Person>().Where(it => it.Id == 1 || it.Name == "a").ToList();
            var getByFuns = db.Queryable<Person>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
            var group = db.Queryable<Person>().GroupBy(it => it.Id).Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();

            int pageIndex = 0, pageSize = 20, totalCount = 0;
            //Page
            var page = db.Queryable<Person>().ToPageList(pageIndex, pageSize, ref totalCount);

            //page join
            //var pageJoin = db.Queryable<Person, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.Id))
            //.ToPageList(pageIndex, pageSize, ref totalCount);

            //top 5
            var top5 = db.Queryable<Person>().Take(5).ToList();

            //join Order By (order by st.id desc,sc.id desc)
            //var list4 = db.Queryable<Person, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.Id))
            //.OrderBy(st => st.Id, OrderByType.Desc)
            //.OrderBy((st, sc) => sc.Id, OrderByType.Desc)
            //.Select<ViewModelStudent>().ToList();
        }

        private void btnCommDB_Click(object sender, RoutedEventArgs e)
        {
            db.CodeFirst.InitTables(typeof(Person));
        }
    }
}
